{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"Bitcoin_Download.ipynb","provenance":[],"collapsed_sections":[],"toc_visible":true,"mount_file_id":"1dhb23Qa6K38Pfc0h-enfhvghO8SPkr0a","authorship_tag":"ABX9TyObZgVxXN9Ce5/0j2h39ATp"},"kernelspec":{"name":"python3","display_name":"Python 3"}},"cells":[{"cell_type":"code","metadata":{"id":"hWpn5QS5vQI_","colab_type":"code","colab":{}},"source":["#transactions per second in a lifespan of 1 year\n","#https://www.blockchain.com/api/charts_api"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"yP1L8TWH2fBc","colab_type":"text"},"source":["##Functions"]},{"cell_type":"code","metadata":{"id":"N6VY8cRdjoPs","colab_type":"code","colab":{}},"source":["def group_chunks(df, id_loc, value_loc): #df, 0, 1\n","  def average(list1):\n","    sum1 = 0\n","    for _ in list1:\n","      sum1 += _\n","    return sum1/len(list1)\n","\n","  #convert DataFrame into a dict with a unique value per timestamp\n","  mydict = {}\n","  for x in range(len(df)):\n","    currentid = df.iloc[x,id_loc]\n","    currentvalue = df.iloc[x,value_loc]\n","    mydict.setdefault(currentid, [])\n","    mydict[currentid].append(currentvalue)\n","  mydict\n","\n","  #convert dict into a list\n","  dictlist = list()\n","  for key, value in mydict.items():\n","    temp = [key,value]\n","    dictlist.append(temp)\n","\n","  #convert to DataFrame\n","  dictlist = pd.DataFrame(dictlist)\n","  dictlist\n","\n","  #average of multiple values\n","  dictlist[1] = dictlist[1].apply(lambda x : average(x))\n","  return dictlist"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"MwhyjQNekIfp","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":34},"executionInfo":{"status":"ok","timestamp":1593982782095,"user_tz":-120,"elapsed":510,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"90a3c835-4ab0-457e-9c9a-0716397eb05a"},"source":["#***cambiare questa per determinare inizio conteggio settimana\n","benchmark_date = 2015\n","\n","import datetime\n","def convert_week(date):\n","  year = int(date[0:4])\n","  return datetime.datetime.strptime(date, '%Y-%m-%d').isocalendar()[1] + (year-benchmark_date)*52\n","convert_week('2018-01-01')\n","convert_week('2019-01-01')\n","\n","def convert_week_back(week_n):\n","  year_week = int(week_n/52)\n","  year = benchmark_date + year_week\n","  d = str(year)+'-W'+str(week_n%52)\n","  r = datetime.datetime.strptime(d + '-1', \"%Y-W%W-%w\")\n","  return r\n","\n","convert_week_back(129)"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["datetime.datetime(2017, 6, 19, 0, 0)"]},"metadata":{"tags":[]},"execution_count":9}]},{"cell_type":"code","metadata":{"id":"4ip2-VzwTpD9","colab_type":"code","colab":{}},"source":["#opening json files from drive, one example\n","f = open('/content/drive/My Drive/Colab Notebooks/Projects_Work/20200617_Bitcoin/fees-usd-per-transaction.json')\n","data = json.load(f)\n","data"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"1CW47b6s0FPS","colab_type":"text"},"source":["##Download Data"]},{"cell_type":"code","metadata":{"id":"51t30wkbrEga","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":402},"executionInfo":{"status":"ok","timestamp":1593981084581,"user_tz":-120,"elapsed":2359,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"ea29ab44-7dfa-4aad-ef9c-a2337359ba3c"},"source":["import json\n","import urllib.request\n","import pandas as pd\n","\n","with urllib.request.urlopen('https://api.blockchain.info/charts/market-cap?timespan=5years&rollingAverage=24hours&format=json') as url:\n","  response = url.read()\n","  data = json.loads(response)\n","\n","import pandas as pd\n","data = pd.DataFrame(data)\n","data"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>status</th>\n","      <th>name</th>\n","      <th>unit</th>\n","      <th>period</th>\n","      <th>description</th>\n","      <th>values</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1436418524, 'y': 3858463442.6750526}</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1436532268, 'y': 3851885351.6221175}</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1436640639, 'y': 3915180780.4732704}</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1436764315, 'y': 4052470543.9329143}</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1436871065, 'y': 4241715527.078092}</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>1496</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1593435074, 'y': 167546872072.3843}</td>\n","    </tr>\n","    <tr>\n","      <th>1497</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1593531853, 'y': 167482788267.95184}</td>\n","    </tr>\n","    <tr>\n","      <th>1498</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1593638587, 'y': 168390473144.07108}</td>\n","    </tr>\n","    <tr>\n","      <th>1499</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1593732338, 'y': 168867619629.73785}</td>\n","    </tr>\n","    <tr>\n","      <th>1500</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1593825029, 'y': 168459089125.6181}</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>1501 rows × 6 columns</p>\n","</div>"],"text/plain":["     status  ...                                      values\n","0        ok  ...  {'x': 1436418524, 'y': 3858463442.6750526}\n","1        ok  ...  {'x': 1436532268, 'y': 3851885351.6221175}\n","2        ok  ...  {'x': 1436640639, 'y': 3915180780.4732704}\n","3        ok  ...  {'x': 1436764315, 'y': 4052470543.9329143}\n","4        ok  ...   {'x': 1436871065, 'y': 4241715527.078092}\n","...     ...  ...                                         ...\n","1496     ok  ...   {'x': 1593435074, 'y': 167546872072.3843}\n","1497     ok  ...  {'x': 1593531853, 'y': 167482788267.95184}\n","1498     ok  ...  {'x': 1593638587, 'y': 168390473144.07108}\n","1499     ok  ...  {'x': 1593732338, 'y': 168867619629.73785}\n","1500     ok  ...   {'x': 1593825029, 'y': 168459089125.6181}\n","\n","[1501 rows x 6 columns]"]},"metadata":{"tags":[]},"execution_count":2}]},{"cell_type":"code","metadata":{"colab_type":"code","id":"lNuM0R9X0KbT","colab":{}},"source":["import json\n","import urllib.request\n","import pandas as pd\n","\n","def download_data(chart_name, data_=None, compress=True):\n","  #https://api.blockchain.info/charts/transactions-per-second?timespan=2years&rollingAverage=8hours&format=json\n","\n","  if data_ is None:\n","    with urllib.request.urlopen('https://api.blockchain.info/charts/' + chart_name + '?timespan=5years&rollingAverage=24hours&format=json') as url:\n","      response = url.read()\n","      data = json.loads(response)\n","  else:\n","    data = open(data_)\n","    data = json.load(data)\n","  \n","  import pandas as pd\n","  data = pd.DataFrame(data)\n","  data\n","\n","  #make backup\n","  data_copy = data.copy()\n","\n","  def extract_time(dict1):\n","    dict2 = list(dict1.values())\n","    #dict2 = list(data['values'][0].values())[0],\n","    return dict2\n","\n","  def time_converter(time1):\n","    from datetime import datetime\n","    #return datetime.fromtimestamp(time1).strftime('%Y-%m-%d %H:%M:%S')\n","    return datetime.fromtimestamp(time1).strftime('%Y-%m-%d')\n","\n","  #preprocessing data\n","  data = data_copy.copy()\n","  data['timestamp'] = data['values'].apply(lambda x : extract_time(x)[0])\n","  data['timestamp'] = data['timestamp'].apply(lambda x : time_converter(x))\n","  data[chart_name] = data['values'].apply(lambda x : extract_time(x)[1])\n","  data.pop('values')\n","  data\n","\n","  df = data.drop(['status', 'name', 'unit', 'period', 'description'], axis=1)\n","  df\n","\n","  if compress == True:\n","    #group by date\n","    df = group_chunks(df, 0, 1)\n","    #df = df.drop(df.index[[730]])\n","    df\n","\n","    #apply weeks\n","    df['week'] = df[0].apply(lambda x : convert_week(x))\n","    df.pop(0)\n","    df\n","\n","    #group by week\n","    df = group_chunks(df, 1, 0)\n","    df\n","\n","  return df"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"k7ToDgNksyUt","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":402},"executionInfo":{"status":"ok","timestamp":1593982822100,"user_tz":-120,"elapsed":10940,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"8d5851d5-21d6-490e-cb30-9485d854ebab"},"source":["market_cap = download_data('market-cap')\n","market_cap.columns = ['week', 'market_cap']\n","market_cap"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>week</th>\n","      <th>market_cap</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>28</td>\n","      <td>3.875177e+09</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>29</td>\n","      <td>4.142558e+09</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>30</td>\n","      <td>4.004468e+09</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>31</td>\n","      <td>4.177792e+09</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>32</td>\n","      <td>4.064795e+09</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>255</th>\n","      <td>283</td>\n","      <td>1.776837e+11</td>\n","    </tr>\n","    <tr>\n","      <th>256</th>\n","      <td>284</td>\n","      <td>1.770631e+11</td>\n","    </tr>\n","    <tr>\n","      <th>257</th>\n","      <td>285</td>\n","      <td>1.726850e+11</td>\n","    </tr>\n","    <tr>\n","      <th>258</th>\n","      <td>286</td>\n","      <td>1.723651e+11</td>\n","    </tr>\n","    <tr>\n","      <th>259</th>\n","      <td>287</td>\n","      <td>1.681494e+11</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>260 rows × 2 columns</p>\n","</div>"],"text/plain":["     week    market_cap\n","0      28  3.875177e+09\n","1      29  4.142558e+09\n","2      30  4.004468e+09\n","3      31  4.177792e+09\n","4      32  4.064795e+09\n","..    ...           ...\n","255   283  1.776837e+11\n","256   284  1.770631e+11\n","257   285  1.726850e+11\n","258   286  1.723651e+11\n","259   287  1.681494e+11\n","\n","[260 rows x 2 columns]"]},"metadata":{"tags":[]},"execution_count":12}]},{"cell_type":"code","metadata":{"id":"RA-ezNIP_Zw7","colab_type":"code","colab":{}},"source":["#download data compressed by week\n","market_cap = download_data('market-cap')\n","tps = download_data('transactions-per-second')\n","market_price =  download_data('market-price')\n","total_bitcoins = download_data('total-bitcoins')\n","miners_revenue = download_data('miners-revenue')\n","\n","#importing data by json, then compressing\n","#transaction_fees = download_data('transaction-fees-usd') #url not working\n","transaction_fees = download_data(0, data_='/content/drive/My Drive/Colab Notebooks/Projects_Work/20200617_Bitcoin/fees-usd-per-transaction.json') #url non funziona"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"dCEEdylo2-7w","colab_type":"code","colab":{}},"source":["#if you want to download a single piece of data\n","market_price =  download_data('market-price', compress=False)\n","market_price\n","market_price.to_csv('bitcoin.csv')"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"colab_type":"text","id":"lx3gY0n8cyzL"},"source":["##merge all datasets"]},{"cell_type":"code","metadata":{"colab_type":"code","id":"8ghkVR4lcyzL","colab":{"base_uri":"https://localhost:8080/","height":419},"executionInfo":{"status":"ok","timestamp":1593623893883,"user_tz":-120,"elapsed":1664,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"65654c8a-f7df-4540-bd85-1a3eb90557cd"},"source":["#tps, market_cap, total_bitcoins, market_price\n","result = pd.merge(tps, market_cap, on=0)\n","result = pd.merge(result, total_bitcoins, on=0)\n","result = pd.merge(result, market_price, on=0)\n","result = pd.merge(result, miners_revenue, on=0)\n","result = pd.merge(result, transaction_fees, on=0)\n","result"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>0</th>\n","      <th>1_x</th>\n","      <th>1_y</th>\n","      <th>1_x</th>\n","      <th>1_y</th>\n","      <th>1_x</th>\n","      <th>1_y</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>75</td>\n","      <td>2.282639</td>\n","      <td>9.040228e+09</td>\n","      <td>1.562784e+07</td>\n","      <td>582.725714</td>\n","      <td>2.319604e+06</td>\n","      <td>0.143732</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>76</td>\n","      <td>2.713914</td>\n","      <td>1.059887e+10</td>\n","      <td>1.565504e+07</td>\n","      <td>716.144286</td>\n","      <td>2.886912e+06</td>\n","      <td>0.222933</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>77</td>\n","      <td>2.497272</td>\n","      <td>1.099238e+10</td>\n","      <td>1.568228e+07</td>\n","      <td>669.740000</td>\n","      <td>2.537684e+06</td>\n","      <td>0.205097</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>78</td>\n","      <td>2.252927</td>\n","      <td>1.019498e+10</td>\n","      <td>1.570727e+07</td>\n","      <td>658.310000</td>\n","      <td>2.617662e+06</td>\n","      <td>0.177134</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>79</td>\n","      <td>2.174107</td>\n","      <td>1.049557e+10</td>\n","      <td>1.573250e+07</td>\n","      <td>661.374286</td>\n","      <td>2.274917e+06</td>\n","      <td>0.170747</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>208</th>\n","      <td>283</td>\n","      <td>3.405134</td>\n","      <td>1.766778e+11</td>\n","      <td>1.839208e+07</td>\n","      <td>9703.917143</td>\n","      <td>1.008126e+07</td>\n","      <td>1.788340</td>\n","    </tr>\n","    <tr>\n","      <th>209</th>\n","      <td>284</td>\n","      <td>3.479638</td>\n","      <td>1.775381e+11</td>\n","      <td>1.839942e+07</td>\n","      <td>9631.944286</td>\n","      <td>1.032949e+07</td>\n","      <td>0.964811</td>\n","    </tr>\n","    <tr>\n","      <th>210</th>\n","      <td>285</td>\n","      <td>3.470908</td>\n","      <td>1.728119e+11</td>\n","      <td>1.840564e+07</td>\n","      <td>9396.455714</td>\n","      <td>8.614685e+06</td>\n","      <td>1.152122</td>\n","    </tr>\n","    <tr>\n","      <th>211</th>\n","      <td>286</td>\n","      <td>3.470784</td>\n","      <td>1.726835e+11</td>\n","      <td>1.841185e+07</td>\n","      <td>9324.897143</td>\n","      <td>8.952018e+06</td>\n","      <td>1.025005</td>\n","    </tr>\n","    <tr>\n","      <th>212</th>\n","      <td>287</td>\n","      <td>3.708160</td>\n","      <td>1.680863e+11</td>\n","      <td>1.841637e+07</td>\n","      <td>9148.930000</td>\n","      <td>8.682023e+06</td>\n","      <td>1.311118</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>213 rows × 7 columns</p>\n","</div>"],"text/plain":["       0       1_x           1_y  ...          1_y           1_x       1_y\n","0     75  2.282639  9.040228e+09  ...   582.725714  2.319604e+06  0.143732\n","1     76  2.713914  1.059887e+10  ...   716.144286  2.886912e+06  0.222933\n","2     77  2.497272  1.099238e+10  ...   669.740000  2.537684e+06  0.205097\n","3     78  2.252927  1.019498e+10  ...   658.310000  2.617662e+06  0.177134\n","4     79  2.174107  1.049557e+10  ...   661.374286  2.274917e+06  0.170747\n","..   ...       ...           ...  ...          ...           ...       ...\n","208  283  3.405134  1.766778e+11  ...  9703.917143  1.008126e+07  1.788340\n","209  284  3.479638  1.775381e+11  ...  9631.944286  1.032949e+07  0.964811\n","210  285  3.470908  1.728119e+11  ...  9396.455714  8.614685e+06  1.152122\n","211  286  3.470784  1.726835e+11  ...  9324.897143  8.952018e+06  1.025005\n","212  287  3.708160  1.680863e+11  ...  9148.930000  8.682023e+06  1.311118\n","\n","[213 rows x 7 columns]"]},"metadata":{"tags":[]},"execution_count":53}]},{"cell_type":"code","metadata":{"id":"bZD4TiE3e4WJ","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":419},"executionInfo":{"status":"ok","timestamp":1593623897940,"user_tz":-120,"elapsed":636,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"37dac480-c638-4339-e286-dfac7bc610f5"},"source":["result.columns = ['week', 'tps', 'market_cap', 'total_bitcoins', 'market_price', 'miners_revenue', 'transaction_fees']\n","result"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>week</th>\n","      <th>tps</th>\n","      <th>market_cap</th>\n","      <th>total_bitcoins</th>\n","      <th>market_price</th>\n","      <th>miners_revenue</th>\n","      <th>transaction_fees</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>75</td>\n","      <td>2.282639</td>\n","      <td>9.040228e+09</td>\n","      <td>1.562784e+07</td>\n","      <td>582.725714</td>\n","      <td>2.319604e+06</td>\n","      <td>0.143732</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>76</td>\n","      <td>2.713914</td>\n","      <td>1.059887e+10</td>\n","      <td>1.565504e+07</td>\n","      <td>716.144286</td>\n","      <td>2.886912e+06</td>\n","      <td>0.222933</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>77</td>\n","      <td>2.497272</td>\n","      <td>1.099238e+10</td>\n","      <td>1.568228e+07</td>\n","      <td>669.740000</td>\n","      <td>2.537684e+06</td>\n","      <td>0.205097</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>78</td>\n","      <td>2.252927</td>\n","      <td>1.019498e+10</td>\n","      <td>1.570727e+07</td>\n","      <td>658.310000</td>\n","      <td>2.617662e+06</td>\n","      <td>0.177134</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>79</td>\n","      <td>2.174107</td>\n","      <td>1.049557e+10</td>\n","      <td>1.573250e+07</td>\n","      <td>661.374286</td>\n","      <td>2.274917e+06</td>\n","      <td>0.170747</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>208</th>\n","      <td>283</td>\n","      <td>3.405134</td>\n","      <td>1.766778e+11</td>\n","      <td>1.839208e+07</td>\n","      <td>9703.917143</td>\n","      <td>1.008126e+07</td>\n","      <td>1.788340</td>\n","    </tr>\n","    <tr>\n","      <th>209</th>\n","      <td>284</td>\n","      <td>3.479638</td>\n","      <td>1.775381e+11</td>\n","      <td>1.839942e+07</td>\n","      <td>9631.944286</td>\n","      <td>1.032949e+07</td>\n","      <td>0.964811</td>\n","    </tr>\n","    <tr>\n","      <th>210</th>\n","      <td>285</td>\n","      <td>3.470908</td>\n","      <td>1.728119e+11</td>\n","      <td>1.840564e+07</td>\n","      <td>9396.455714</td>\n","      <td>8.614685e+06</td>\n","      <td>1.152122</td>\n","    </tr>\n","    <tr>\n","      <th>211</th>\n","      <td>286</td>\n","      <td>3.470784</td>\n","      <td>1.726835e+11</td>\n","      <td>1.841185e+07</td>\n","      <td>9324.897143</td>\n","      <td>8.952018e+06</td>\n","      <td>1.025005</td>\n","    </tr>\n","    <tr>\n","      <th>212</th>\n","      <td>287</td>\n","      <td>3.708160</td>\n","      <td>1.680863e+11</td>\n","      <td>1.841637e+07</td>\n","      <td>9148.930000</td>\n","      <td>8.682023e+06</td>\n","      <td>1.311118</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>213 rows × 7 columns</p>\n","</div>"],"text/plain":["     week       tps  ...  miners_revenue  transaction_fees\n","0      75  2.282639  ...    2.319604e+06          0.143732\n","1      76  2.713914  ...    2.886912e+06          0.222933\n","2      77  2.497272  ...    2.537684e+06          0.205097\n","3      78  2.252927  ...    2.617662e+06          0.177134\n","4      79  2.174107  ...    2.274917e+06          0.170747\n","..    ...       ...  ...             ...               ...\n","208   283  3.405134  ...    1.008126e+07          1.788340\n","209   284  3.479638  ...    1.032949e+07          0.964811\n","210   285  3.470908  ...    8.614685e+06          1.152122\n","211   286  3.470784  ...    8.952018e+06          1.025005\n","212   287  3.708160  ...    8.682023e+06          1.311118\n","\n","[213 rows x 7 columns]"]},"metadata":{"tags":[]},"execution_count":54}]},{"cell_type":"markdown","metadata":{"id":"AhKB_ac_3S5N","colab_type":"text"},"source":["##CPI"]},{"cell_type":"code","metadata":{"id":"aYJYv7kS3T6N","colab_type":"code","colab":{}},"source":["#https://inflationdata.com/Inflation/Consumer_Price_Index/HistoricalCPI.aspx?reloaded=true\n","#https://www.usinflationcalculator.com/inflation/consumer-price-index-and-annual-percent-changes-from-1913-to-2008/"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"EcI4o_dI5Am7","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":419},"executionInfo":{"status":"ok","timestamp":1593623903968,"user_tz":-120,"elapsed":597,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"37140c37-2fc3-4105-ab74-eaa4edc2c076"},"source":["import datetime\n","\n","#CPI\n","CPI = [\n","       '233.707',\t '234.722',\t '236.119',\t '236.599',\t '237.805',\t '238.638',\t '238.654',\t '238.316',\t '237.945',\t '237.838',\t '237.336',\t '236.525',\t \n","       '236.916',\t '237.111',\t '238.132',\t '239.261',\t '240.236',\t '241.038',\t '240.647',\t '240.853',\t '241.428',\t '241.729',\t '241.353',\t '241.432',\t \n","       '242.839',\t '243.603',\t '243.801',\t '244.524',\t '244.733',\t '244.955',\t '244.786',\t '245.519',\t '246.819',\t '246.663',\t '246.669',\t '246.524',\t \n","       '247.867',\t '248.991',\t '249.554',\t '250.546',\t '251.588',\t '251.989',\t '252.006',\t '252.146',\t '252.439',\t '252.885',\t '252.038',\t '251.233',\t \n","       '251.712',\t '252.776',\t '254.202',\t '255.548',\t '256.092',\t '256.143',\t '256.571',\t '256.558',\t '256.759',\t '257.346',\t '257.208',\t '256.974',\t \n","       '257.971',\t '258.678',\t '258.115',\t '256.389',\t '256.394', '256.399', '256.405'\n","      ]\n","      \n","CPI = pd.DataFrame(CPI)\n","CPI\n","\n","#date\n","date_list = list()\n","for year in range(2015, 2020):\n","  for month in range(1, 13):\n","    date_list.append(datetime.datetime(year, month, 1).strftime('%Y-%m-%d'))\n","date_list.append(datetime.datetime(2020, 1, 1).strftime('%Y-%m-%d'))\n","date_list.append(datetime.datetime(2020, 2, 1).strftime('%Y-%m-%d'))\n","date_list.append(datetime.datetime(2020, 3, 1).strftime('%Y-%m-%d'))\n","date_list.append(datetime.datetime(2020, 4, 1).strftime('%Y-%m-%d'))\n","date_list.append(datetime.datetime(2020, 5, 1).strftime('%Y-%m-%d'))\n","date_list.append(datetime.datetime(2020, 6, 1).strftime('%Y-%m-%d'))\n","date_list.append(datetime.datetime(2020, 7, 1).strftime('%Y-%m-%d'))\n","date_list = pd.DataFrame(date_list)\n","\n","#concatenation\n","CPI = pd.concat([date_list, CPI], axis=1)\n","CPI.columns = ['date', 'CPI']\n","CPI"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>date</th>\n","      <th>CPI</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>2015-01-01</td>\n","      <td>233.707</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>2015-02-01</td>\n","      <td>234.722</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>2015-03-01</td>\n","      <td>236.119</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>2015-04-01</td>\n","      <td>236.599</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>2015-05-01</td>\n","      <td>237.805</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>62</th>\n","      <td>2020-03-01</td>\n","      <td>258.115</td>\n","    </tr>\n","    <tr>\n","      <th>63</th>\n","      <td>2020-04-01</td>\n","      <td>256.389</td>\n","    </tr>\n","    <tr>\n","      <th>64</th>\n","      <td>2020-05-01</td>\n","      <td>256.394</td>\n","    </tr>\n","    <tr>\n","      <th>65</th>\n","      <td>2020-06-01</td>\n","      <td>256.399</td>\n","    </tr>\n","    <tr>\n","      <th>66</th>\n","      <td>2020-07-01</td>\n","      <td>256.405</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>67 rows × 2 columns</p>\n","</div>"],"text/plain":["          date      CPI\n","0   2015-01-01  233.707\n","1   2015-02-01  234.722\n","2   2015-03-01  236.119\n","3   2015-04-01  236.599\n","4   2015-05-01  237.805\n","..         ...      ...\n","62  2020-03-01  258.115\n","63  2020-04-01  256.389\n","64  2020-05-01  256.394\n","65  2020-06-01  256.399\n","66  2020-07-01  256.405\n","\n","[67 rows x 2 columns]"]},"metadata":{"tags":[]},"execution_count":55}]},{"cell_type":"code","metadata":{"id":"tLdzT-gVQEcV","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":419},"executionInfo":{"status":"ok","timestamp":1593623907293,"user_tz":-120,"elapsed":615,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"25250e03-1367-495c-b0b2-7b845e75d254"},"source":["import datetime\n","import pandas as pd\n","base = (datetime.datetime(2015, 1, 1))\n","date_list = [base + datetime.timedelta(days=x) for x in range(2000)]\n","\n","date_list_ = list()\n","for _ in date_list:\n","  date_list_.append(_.strftime('%Y-%m-%d'))\n","date_list_[len(date_list_)-1]\n","\n","date_list = pd.DataFrame(date_list_)\n","date_list['CPI'] = 0.0\n","date_list.columns = ['date', 'CPI']\n","date_list\n","\n","CPI = date_list.merge(CPI, on='date', how='outer')\n","#result['CPI_y'].fillna(0, inplace=True)\n","CPI.pop('CPI_x')\n","CPI['CPI_y'] = CPI['CPI_y'].values.astype(float)\n","CPI\n","CPI['CPI_y'].interpolate(method='polynomial', order=3, inplace=True)\n","CPI"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>date</th>\n","      <th>CPI_y</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>2015-01-01</td>\n","      <td>233.707000</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>2015-01-02</td>\n","      <td>233.701194</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>2015-01-03</td>\n","      <td>233.698951</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>2015-01-04</td>\n","      <td>233.700170</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>2015-01-05</td>\n","      <td>233.704748</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>1996</th>\n","      <td>2020-06-19</td>\n","      <td>256.384623</td>\n","    </tr>\n","    <tr>\n","      <th>1997</th>\n","      <td>2020-06-20</td>\n","      <td>256.388604</td>\n","    </tr>\n","    <tr>\n","      <th>1998</th>\n","      <td>2020-06-21</td>\n","      <td>256.393306</td>\n","    </tr>\n","    <tr>\n","      <th>1999</th>\n","      <td>2020-06-22</td>\n","      <td>256.398760</td>\n","    </tr>\n","    <tr>\n","      <th>2000</th>\n","      <td>2020-07-01</td>\n","      <td>256.405000</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>2001 rows × 2 columns</p>\n","</div>"],"text/plain":["            date       CPI_y\n","0     2015-01-01  233.707000\n","1     2015-01-02  233.701194\n","2     2015-01-03  233.698951\n","3     2015-01-04  233.700170\n","4     2015-01-05  233.704748\n","...          ...         ...\n","1996  2020-06-19  256.384623\n","1997  2020-06-20  256.388604\n","1998  2020-06-21  256.393306\n","1999  2020-06-22  256.398760\n","2000  2020-07-01  256.405000\n","\n","[2001 rows x 2 columns]"]},"metadata":{"tags":[]},"execution_count":56}]},{"cell_type":"code","metadata":{"id":"LxHv-KoeYqya","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":669},"executionInfo":{"status":"ok","timestamp":1593623909517,"user_tz":-120,"elapsed":667,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"ecda2ad8-2071-4363-e8f3-f5ff90667b79"},"source":["#group by date\n","CPI = group_chunks(CPI, 0, 1)\n","CPI\n","\n","#apply weeks\n","CPI['week'] = CPI[0].apply(lambda x : convert_week(x))\n","CPI.pop(0)\n","\n","#errore in riga 731: valore a posto, ma settimana no\n","CPI[720:740]"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>1</th>\n","      <th>week</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>720</th>\n","      <td>242.306280</td>\n","      <td>103</td>\n","    </tr>\n","    <tr>\n","      <th>721</th>\n","      <td>242.357386</td>\n","      <td>103</td>\n","    </tr>\n","    <tr>\n","      <th>722</th>\n","      <td>242.408316</td>\n","      <td>103</td>\n","    </tr>\n","    <tr>\n","      <th>723</th>\n","      <td>242.458951</td>\n","      <td>103</td>\n","    </tr>\n","    <tr>\n","      <th>724</th>\n","      <td>242.509170</td>\n","      <td>103</td>\n","    </tr>\n","    <tr>\n","      <th>725</th>\n","      <td>242.558854</td>\n","      <td>104</td>\n","    </tr>\n","    <tr>\n","      <th>726</th>\n","      <td>242.607883</td>\n","      <td>104</td>\n","    </tr>\n","    <tr>\n","      <th>727</th>\n","      <td>242.656136</td>\n","      <td>104</td>\n","    </tr>\n","    <tr>\n","      <th>728</th>\n","      <td>242.703495</td>\n","      <td>104</td>\n","    </tr>\n","    <tr>\n","      <th>729</th>\n","      <td>242.749838</td>\n","      <td>104</td>\n","    </tr>\n","    <tr>\n","      <th>730</th>\n","      <td>242.795047</td>\n","      <td>104</td>\n","    </tr>\n","    <tr>\n","      <th>731</th>\n","      <td>242.839000</td>\n","      <td>156</td>\n","    </tr>\n","    <tr>\n","      <th>732</th>\n","      <td>242.881601</td>\n","      <td>105</td>\n","    </tr>\n","    <tr>\n","      <th>733</th>\n","      <td>242.922844</td>\n","      <td>105</td>\n","    </tr>\n","    <tr>\n","      <th>734</th>\n","      <td>242.962746</td>\n","      <td>105</td>\n","    </tr>\n","    <tr>\n","      <th>735</th>\n","      <td>243.001323</td>\n","      <td>105</td>\n","    </tr>\n","    <tr>\n","      <th>736</th>\n","      <td>243.038591</td>\n","      <td>105</td>\n","    </tr>\n","    <tr>\n","      <th>737</th>\n","      <td>243.074568</td>\n","      <td>105</td>\n","    </tr>\n","    <tr>\n","      <th>738</th>\n","      <td>243.109270</td>\n","      <td>105</td>\n","    </tr>\n","    <tr>\n","      <th>739</th>\n","      <td>243.142714</td>\n","      <td>106</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["              1  week\n","720  242.306280   103\n","721  242.357386   103\n","722  242.408316   103\n","723  242.458951   103\n","724  242.509170   103\n","725  242.558854   104\n","726  242.607883   104\n","727  242.656136   104\n","728  242.703495   104\n","729  242.749838   104\n","730  242.795047   104\n","731  242.839000   156\n","732  242.881601   105\n","733  242.922844   105\n","734  242.962746   105\n","735  243.001323   105\n","736  243.038591   105\n","737  243.074568   105\n","738  243.109270   105\n","739  243.142714   106"]},"metadata":{"tags":[]},"execution_count":57}]},{"cell_type":"code","metadata":{"id":"P1A2MSg6d3Xv","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":359},"executionInfo":{"status":"ok","timestamp":1593623912598,"user_tz":-120,"elapsed":735,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"7d1649ed-d4f9-49fb-f92a-5df763e560e9"},"source":["#group by week\n","CPI = group_chunks(CPI, 1, 0)\n","CPI.columns = ['week', 'CPI']\n","CPI[100:110]"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>week</th>\n","      <th>CPI</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>100</th>\n","      <td>100</td>\n","      <td>241.436529</td>\n","    </tr>\n","    <tr>\n","      <th>101</th>\n","      <td>101</td>\n","      <td>241.683508</td>\n","    </tr>\n","    <tr>\n","      <th>102</th>\n","      <td>102</td>\n","      <td>242.004003</td>\n","    </tr>\n","    <tr>\n","      <th>103</th>\n","      <td>103</td>\n","      <td>242.357035</td>\n","    </tr>\n","    <tr>\n","      <th>104</th>\n","      <td>104</td>\n","      <td>242.678542</td>\n","    </tr>\n","    <tr>\n","      <th>105</th>\n","      <td>156</td>\n","      <td>247.060307</td>\n","    </tr>\n","    <tr>\n","      <th>106</th>\n","      <td>106</td>\n","      <td>243.233280</td>\n","    </tr>\n","    <tr>\n","      <th>107</th>\n","      <td>107</td>\n","      <td>243.409469</td>\n","    </tr>\n","    <tr>\n","      <th>108</th>\n","      <td>108</td>\n","      <td>243.532992</td>\n","    </tr>\n","    <tr>\n","      <th>109</th>\n","      <td>109</td>\n","      <td>243.609715</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["     week         CPI\n","100   100  241.436529\n","101   101  241.683508\n","102   102  242.004003\n","103   103  242.357035\n","104   104  242.678542\n","105   156  247.060307\n","106   106  243.233280\n","107   107  243.409469\n","108   108  243.532992\n","109   109  243.609715"]},"metadata":{"tags":[]},"execution_count":58}]},{"cell_type":"code","metadata":{"id":"s53IEWR4iXcp","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":529},"executionInfo":{"status":"ok","timestamp":1593623914422,"user_tz":-120,"elapsed":626,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"b76bd333-9e25-4cb5-f98c-b53fbf7f7606"},"source":["CPI['week'][105] = 105\n","CPI['CPI'][105] = 242.878542\n","CPI[100:110]"],"execution_count":null,"outputs":[{"output_type":"stream","text":["/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n","A value is trying to be set on a copy of a slice from a DataFrame\n","\n","See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n","  \"\"\"Entry point for launching an IPython kernel.\n","/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n","A value is trying to be set on a copy of a slice from a DataFrame\n","\n","See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n","  \n"],"name":"stderr"},{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>week</th>\n","      <th>CPI</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>100</th>\n","      <td>100</td>\n","      <td>241.436529</td>\n","    </tr>\n","    <tr>\n","      <th>101</th>\n","      <td>101</td>\n","      <td>241.683508</td>\n","    </tr>\n","    <tr>\n","      <th>102</th>\n","      <td>102</td>\n","      <td>242.004003</td>\n","    </tr>\n","    <tr>\n","      <th>103</th>\n","      <td>103</td>\n","      <td>242.357035</td>\n","    </tr>\n","    <tr>\n","      <th>104</th>\n","      <td>104</td>\n","      <td>242.678542</td>\n","    </tr>\n","    <tr>\n","      <th>105</th>\n","      <td>105</td>\n","      <td>242.878542</td>\n","    </tr>\n","    <tr>\n","      <th>106</th>\n","      <td>106</td>\n","      <td>243.233280</td>\n","    </tr>\n","    <tr>\n","      <th>107</th>\n","      <td>107</td>\n","      <td>243.409469</td>\n","    </tr>\n","    <tr>\n","      <th>108</th>\n","      <td>108</td>\n","      <td>243.532992</td>\n","    </tr>\n","    <tr>\n","      <th>109</th>\n","      <td>109</td>\n","      <td>243.609715</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["     week         CPI\n","100   100  241.436529\n","101   101  241.683508\n","102   102  242.004003\n","103   103  242.357035\n","104   104  242.678542\n","105   105  242.878542\n","106   106  243.233280\n","107   107  243.409469\n","108   108  243.532992\n","109   109  243.609715"]},"metadata":{"tags":[]},"execution_count":59}]},{"cell_type":"code","metadata":{"id":"BujFmbDjZIyG","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":419},"executionInfo":{"status":"ok","timestamp":1593623917175,"user_tz":-120,"elapsed":658,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"50fc4c1b-e3b1-40da-f456-0ea101a9adab"},"source":["df = pd.merge(result, CPI, on='week')\n","df"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>week</th>\n","      <th>tps</th>\n","      <th>market_cap</th>\n","      <th>total_bitcoins</th>\n","      <th>market_price</th>\n","      <th>miners_revenue</th>\n","      <th>transaction_fees</th>\n","      <th>CPI</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>75</td>\n","      <td>2.282639</td>\n","      <td>9.040228e+09</td>\n","      <td>1.562784e+07</td>\n","      <td>582.725714</td>\n","      <td>2.319604e+06</td>\n","      <td>0.143732</td>\n","      <td>240.995896</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>76</td>\n","      <td>2.713914</td>\n","      <td>1.059887e+10</td>\n","      <td>1.565504e+07</td>\n","      <td>716.144286</td>\n","      <td>2.886912e+06</td>\n","      <td>0.222933</td>\n","      <td>240.890890</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>77</td>\n","      <td>2.497272</td>\n","      <td>1.099238e+10</td>\n","      <td>1.568228e+07</td>\n","      <td>669.740000</td>\n","      <td>2.537684e+06</td>\n","      <td>0.205097</td>\n","      <td>240.764425</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>78</td>\n","      <td>2.252927</td>\n","      <td>1.019498e+10</td>\n","      <td>1.570727e+07</td>\n","      <td>658.310000</td>\n","      <td>2.617662e+06</td>\n","      <td>0.177134</td>\n","      <td>240.660392</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>79</td>\n","      <td>2.174107</td>\n","      <td>1.049557e+10</td>\n","      <td>1.573250e+07</td>\n","      <td>661.374286</td>\n","      <td>2.274917e+06</td>\n","      <td>0.170747</td>\n","      <td>240.614773</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>208</th>\n","      <td>283</td>\n","      <td>3.405134</td>\n","      <td>1.766778e+11</td>\n","      <td>1.839208e+07</td>\n","      <td>9703.917143</td>\n","      <td>1.008126e+07</td>\n","      <td>1.788340</td>\n","      <td>256.389586</td>\n","    </tr>\n","    <tr>\n","      <th>209</th>\n","      <td>284</td>\n","      <td>3.479638</td>\n","      <td>1.775381e+11</td>\n","      <td>1.839942e+07</td>\n","      <td>9631.944286</td>\n","      <td>1.032949e+07</td>\n","      <td>0.964811</td>\n","      <td>256.375644</td>\n","    </tr>\n","    <tr>\n","      <th>210</th>\n","      <td>285</td>\n","      <td>3.470908</td>\n","      <td>1.728119e+11</td>\n","      <td>1.840564e+07</td>\n","      <td>9396.455714</td>\n","      <td>8.614685e+06</td>\n","      <td>1.152122</td>\n","      <td>256.382639</td>\n","    </tr>\n","    <tr>\n","      <th>211</th>\n","      <td>286</td>\n","      <td>3.470784</td>\n","      <td>1.726835e+11</td>\n","      <td>1.841185e+07</td>\n","      <td>9324.897143</td>\n","      <td>8.952018e+06</td>\n","      <td>1.025005</td>\n","      <td>256.398760</td>\n","    </tr>\n","    <tr>\n","      <th>212</th>\n","      <td>287</td>\n","      <td>3.708160</td>\n","      <td>1.680863e+11</td>\n","      <td>1.841637e+07</td>\n","      <td>9148.930000</td>\n","      <td>8.682023e+06</td>\n","      <td>1.311118</td>\n","      <td>256.405000</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>213 rows × 8 columns</p>\n","</div>"],"text/plain":["     week       tps    market_cap  ...  miners_revenue  transaction_fees         CPI\n","0      75  2.282639  9.040228e+09  ...    2.319604e+06          0.143732  240.995896\n","1      76  2.713914  1.059887e+10  ...    2.886912e+06          0.222933  240.890890\n","2      77  2.497272  1.099238e+10  ...    2.537684e+06          0.205097  240.764425\n","3      78  2.252927  1.019498e+10  ...    2.617662e+06          0.177134  240.660392\n","4      79  2.174107  1.049557e+10  ...    2.274917e+06          0.170747  240.614773\n","..    ...       ...           ...  ...             ...               ...         ...\n","208   283  3.405134  1.766778e+11  ...    1.008126e+07          1.788340  256.389586\n","209   284  3.479638  1.775381e+11  ...    1.032949e+07          0.964811  256.375644\n","210   285  3.470908  1.728119e+11  ...    8.614685e+06          1.152122  256.382639\n","211   286  3.470784  1.726835e+11  ...    8.952018e+06          1.025005  256.398760\n","212   287  3.708160  1.680863e+11  ...    8.682023e+06          1.311118  256.405000\n","\n","[213 rows x 8 columns]"]},"metadata":{"tags":[]},"execution_count":60}]},{"cell_type":"code","metadata":{"id":"lb7N-st6ft-R","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":419},"executionInfo":{"status":"ok","timestamp":1593623920359,"user_tz":-120,"elapsed":631,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"879d0e97-eeeb-4928-f3b1-e26c8c9ab635"},"source":["df = df.drop(df.index[[30]])\n","df"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>week</th>\n","      <th>tps</th>\n","      <th>market_cap</th>\n","      <th>total_bitcoins</th>\n","      <th>market_price</th>\n","      <th>miners_revenue</th>\n","      <th>transaction_fees</th>\n","      <th>CPI</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>75</td>\n","      <td>2.282639</td>\n","      <td>9.040228e+09</td>\n","      <td>1.562784e+07</td>\n","      <td>582.725714</td>\n","      <td>2.319604e+06</td>\n","      <td>0.143732</td>\n","      <td>240.995896</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>76</td>\n","      <td>2.713914</td>\n","      <td>1.059887e+10</td>\n","      <td>1.565504e+07</td>\n","      <td>716.144286</td>\n","      <td>2.886912e+06</td>\n","      <td>0.222933</td>\n","      <td>240.890890</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>77</td>\n","      <td>2.497272</td>\n","      <td>1.099238e+10</td>\n","      <td>1.568228e+07</td>\n","      <td>669.740000</td>\n","      <td>2.537684e+06</td>\n","      <td>0.205097</td>\n","      <td>240.764425</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>78</td>\n","      <td>2.252927</td>\n","      <td>1.019498e+10</td>\n","      <td>1.570727e+07</td>\n","      <td>658.310000</td>\n","      <td>2.617662e+06</td>\n","      <td>0.177134</td>\n","      <td>240.660392</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>79</td>\n","      <td>2.174107</td>\n","      <td>1.049557e+10</td>\n","      <td>1.573250e+07</td>\n","      <td>661.374286</td>\n","      <td>2.274917e+06</td>\n","      <td>0.170747</td>\n","      <td>240.614773</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>208</th>\n","      <td>283</td>\n","      <td>3.405134</td>\n","      <td>1.766778e+11</td>\n","      <td>1.839208e+07</td>\n","      <td>9703.917143</td>\n","      <td>1.008126e+07</td>\n","      <td>1.788340</td>\n","      <td>256.389586</td>\n","    </tr>\n","    <tr>\n","      <th>209</th>\n","      <td>284</td>\n","      <td>3.479638</td>\n","      <td>1.775381e+11</td>\n","      <td>1.839942e+07</td>\n","      <td>9631.944286</td>\n","      <td>1.032949e+07</td>\n","      <td>0.964811</td>\n","      <td>256.375644</td>\n","    </tr>\n","    <tr>\n","      <th>210</th>\n","      <td>285</td>\n","      <td>3.470908</td>\n","      <td>1.728119e+11</td>\n","      <td>1.840564e+07</td>\n","      <td>9396.455714</td>\n","      <td>8.614685e+06</td>\n","      <td>1.152122</td>\n","      <td>256.382639</td>\n","    </tr>\n","    <tr>\n","      <th>211</th>\n","      <td>286</td>\n","      <td>3.470784</td>\n","      <td>1.726835e+11</td>\n","      <td>1.841185e+07</td>\n","      <td>9324.897143</td>\n","      <td>8.952018e+06</td>\n","      <td>1.025005</td>\n","      <td>256.398760</td>\n","    </tr>\n","    <tr>\n","      <th>212</th>\n","      <td>287</td>\n","      <td>3.708160</td>\n","      <td>1.680863e+11</td>\n","      <td>1.841637e+07</td>\n","      <td>9148.930000</td>\n","      <td>8.682023e+06</td>\n","      <td>1.311118</td>\n","      <td>256.405000</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>212 rows × 8 columns</p>\n","</div>"],"text/plain":["     week       tps    market_cap  ...  miners_revenue  transaction_fees         CPI\n","0      75  2.282639  9.040228e+09  ...    2.319604e+06          0.143732  240.995896\n","1      76  2.713914  1.059887e+10  ...    2.886912e+06          0.222933  240.890890\n","2      77  2.497272  1.099238e+10  ...    2.537684e+06          0.205097  240.764425\n","3      78  2.252927  1.019498e+10  ...    2.617662e+06          0.177134  240.660392\n","4      79  2.174107  1.049557e+10  ...    2.274917e+06          0.170747  240.614773\n","..    ...       ...           ...  ...             ...               ...         ...\n","208   283  3.405134  1.766778e+11  ...    1.008126e+07          1.788340  256.389586\n","209   284  3.479638  1.775381e+11  ...    1.032949e+07          0.964811  256.375644\n","210   285  3.470908  1.728119e+11  ...    8.614685e+06          1.152122  256.382639\n","211   286  3.470784  1.726835e+11  ...    8.952018e+06          1.025005  256.398760\n","212   287  3.708160  1.680863e+11  ...    8.682023e+06          1.311118  256.405000\n","\n","[212 rows x 8 columns]"]},"metadata":{"tags":[]},"execution_count":61}]},{"cell_type":"code","metadata":{"id":"8Kml3MeDjhSu","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":419},"executionInfo":{"status":"ok","timestamp":1593623925590,"user_tz":-120,"elapsed":2560,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"ca29af20-a2a2-4a89-fa7e-7f4a55b49aa7"},"source":["df = df.reset_index()\n","df.pop('index')\n","df"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>week</th>\n","      <th>tps</th>\n","      <th>market_cap</th>\n","      <th>total_bitcoins</th>\n","      <th>market_price</th>\n","      <th>miners_revenue</th>\n","      <th>transaction_fees</th>\n","      <th>CPI</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>75</td>\n","      <td>2.282639</td>\n","      <td>9.040228e+09</td>\n","      <td>1.562784e+07</td>\n","      <td>582.725714</td>\n","      <td>2.319604e+06</td>\n","      <td>0.143732</td>\n","      <td>240.995896</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>76</td>\n","      <td>2.713914</td>\n","      <td>1.059887e+10</td>\n","      <td>1.565504e+07</td>\n","      <td>716.144286</td>\n","      <td>2.886912e+06</td>\n","      <td>0.222933</td>\n","      <td>240.890890</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>77</td>\n","      <td>2.497272</td>\n","      <td>1.099238e+10</td>\n","      <td>1.568228e+07</td>\n","      <td>669.740000</td>\n","      <td>2.537684e+06</td>\n","      <td>0.205097</td>\n","      <td>240.764425</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>78</td>\n","      <td>2.252927</td>\n","      <td>1.019498e+10</td>\n","      <td>1.570727e+07</td>\n","      <td>658.310000</td>\n","      <td>2.617662e+06</td>\n","      <td>0.177134</td>\n","      <td>240.660392</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>79</td>\n","      <td>2.174107</td>\n","      <td>1.049557e+10</td>\n","      <td>1.573250e+07</td>\n","      <td>661.374286</td>\n","      <td>2.274917e+06</td>\n","      <td>0.170747</td>\n","      <td>240.614773</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>207</th>\n","      <td>283</td>\n","      <td>3.405134</td>\n","      <td>1.766778e+11</td>\n","      <td>1.839208e+07</td>\n","      <td>9703.917143</td>\n","      <td>1.008126e+07</td>\n","      <td>1.788340</td>\n","      <td>256.389586</td>\n","    </tr>\n","    <tr>\n","      <th>208</th>\n","      <td>284</td>\n","      <td>3.479638</td>\n","      <td>1.775381e+11</td>\n","      <td>1.839942e+07</td>\n","      <td>9631.944286</td>\n","      <td>1.032949e+07</td>\n","      <td>0.964811</td>\n","      <td>256.375644</td>\n","    </tr>\n","    <tr>\n","      <th>209</th>\n","      <td>285</td>\n","      <td>3.470908</td>\n","      <td>1.728119e+11</td>\n","      <td>1.840564e+07</td>\n","      <td>9396.455714</td>\n","      <td>8.614685e+06</td>\n","      <td>1.152122</td>\n","      <td>256.382639</td>\n","    </tr>\n","    <tr>\n","      <th>210</th>\n","      <td>286</td>\n","      <td>3.470784</td>\n","      <td>1.726835e+11</td>\n","      <td>1.841185e+07</td>\n","      <td>9324.897143</td>\n","      <td>8.952018e+06</td>\n","      <td>1.025005</td>\n","      <td>256.398760</td>\n","    </tr>\n","    <tr>\n","      <th>211</th>\n","      <td>287</td>\n","      <td>3.708160</td>\n","      <td>1.680863e+11</td>\n","      <td>1.841637e+07</td>\n","      <td>9148.930000</td>\n","      <td>8.682023e+06</td>\n","      <td>1.311118</td>\n","      <td>256.405000</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>212 rows × 8 columns</p>\n","</div>"],"text/plain":["     week       tps    market_cap  ...  miners_revenue  transaction_fees         CPI\n","0      75  2.282639  9.040228e+09  ...    2.319604e+06          0.143732  240.995896\n","1      76  2.713914  1.059887e+10  ...    2.886912e+06          0.222933  240.890890\n","2      77  2.497272  1.099238e+10  ...    2.537684e+06          0.205097  240.764425\n","3      78  2.252927  1.019498e+10  ...    2.617662e+06          0.177134  240.660392\n","4      79  2.174107  1.049557e+10  ...    2.274917e+06          0.170747  240.614773\n","..    ...       ...           ...  ...             ...               ...         ...\n","207   283  3.405134  1.766778e+11  ...    1.008126e+07          1.788340  256.389586\n","208   284  3.479638  1.775381e+11  ...    1.032949e+07          0.964811  256.375644\n","209   285  3.470908  1.728119e+11  ...    8.614685e+06          1.152122  256.382639\n","210   286  3.470784  1.726835e+11  ...    8.952018e+06          1.025005  256.398760\n","211   287  3.708160  1.680863e+11  ...    8.682023e+06          1.311118  256.405000\n","\n","[212 rows x 8 columns]"]},"metadata":{"tags":[]},"execution_count":62}]},{"cell_type":"code","metadata":{"id":"PcFAvM6gkanb","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":419},"executionInfo":{"status":"ok","timestamp":1593623931372,"user_tz":-120,"elapsed":468,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"e261094b-fc4c-49c7-89c3-87ee2eaa4a7c"},"source":["df['timestamp'] = df['week'].apply(lambda x : convert_week_back(x))\n","df"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>week</th>\n","      <th>tps</th>\n","      <th>market_cap</th>\n","      <th>total_bitcoins</th>\n","      <th>market_price</th>\n","      <th>miners_revenue</th>\n","      <th>transaction_fees</th>\n","      <th>CPI</th>\n","      <th>timestamp</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>75</td>\n","      <td>2.282639</td>\n","      <td>9.040228e+09</td>\n","      <td>1.562784e+07</td>\n","      <td>582.725714</td>\n","      <td>2.319604e+06</td>\n","      <td>0.143732</td>\n","      <td>240.995896</td>\n","      <td>2016-06-06</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>76</td>\n","      <td>2.713914</td>\n","      <td>1.059887e+10</td>\n","      <td>1.565504e+07</td>\n","      <td>716.144286</td>\n","      <td>2.886912e+06</td>\n","      <td>0.222933</td>\n","      <td>240.890890</td>\n","      <td>2016-06-13</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>77</td>\n","      <td>2.497272</td>\n","      <td>1.099238e+10</td>\n","      <td>1.568228e+07</td>\n","      <td>669.740000</td>\n","      <td>2.537684e+06</td>\n","      <td>0.205097</td>\n","      <td>240.764425</td>\n","      <td>2016-06-20</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>78</td>\n","      <td>2.252927</td>\n","      <td>1.019498e+10</td>\n","      <td>1.570727e+07</td>\n","      <td>658.310000</td>\n","      <td>2.617662e+06</td>\n","      <td>0.177134</td>\n","      <td>240.660392</td>\n","      <td>2016-06-27</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>79</td>\n","      <td>2.174107</td>\n","      <td>1.049557e+10</td>\n","      <td>1.573250e+07</td>\n","      <td>661.374286</td>\n","      <td>2.274917e+06</td>\n","      <td>0.170747</td>\n","      <td>240.614773</td>\n","      <td>2016-07-04</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>207</th>\n","      <td>283</td>\n","      <td>3.405134</td>\n","      <td>1.766778e+11</td>\n","      <td>1.839208e+07</td>\n","      <td>9703.917143</td>\n","      <td>1.008126e+07</td>\n","      <td>1.788340</td>\n","      <td>256.389586</td>\n","      <td>2020-06-08</td>\n","    </tr>\n","    <tr>\n","      <th>208</th>\n","      <td>284</td>\n","      <td>3.479638</td>\n","      <td>1.775381e+11</td>\n","      <td>1.839942e+07</td>\n","      <td>9631.944286</td>\n","      <td>1.032949e+07</td>\n","      <td>0.964811</td>\n","      <td>256.375644</td>\n","      <td>2020-06-15</td>\n","    </tr>\n","    <tr>\n","      <th>209</th>\n","      <td>285</td>\n","      <td>3.470908</td>\n","      <td>1.728119e+11</td>\n","      <td>1.840564e+07</td>\n","      <td>9396.455714</td>\n","      <td>8.614685e+06</td>\n","      <td>1.152122</td>\n","      <td>256.382639</td>\n","      <td>2020-06-22</td>\n","    </tr>\n","    <tr>\n","      <th>210</th>\n","      <td>286</td>\n","      <td>3.470784</td>\n","      <td>1.726835e+11</td>\n","      <td>1.841185e+07</td>\n","      <td>9324.897143</td>\n","      <td>8.952018e+06</td>\n","      <td>1.025005</td>\n","      <td>256.398760</td>\n","      <td>2020-06-29</td>\n","    </tr>\n","    <tr>\n","      <th>211</th>\n","      <td>287</td>\n","      <td>3.708160</td>\n","      <td>1.680863e+11</td>\n","      <td>1.841637e+07</td>\n","      <td>9148.930000</td>\n","      <td>8.682023e+06</td>\n","      <td>1.311118</td>\n","      <td>256.405000</td>\n","      <td>2020-07-06</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>212 rows × 9 columns</p>\n","</div>"],"text/plain":["     week       tps    market_cap  ...  transaction_fees         CPI  timestamp\n","0      75  2.282639  9.040228e+09  ...          0.143732  240.995896 2016-06-06\n","1      76  2.713914  1.059887e+10  ...          0.222933  240.890890 2016-06-13\n","2      77  2.497272  1.099238e+10  ...          0.205097  240.764425 2016-06-20\n","3      78  2.252927  1.019498e+10  ...          0.177134  240.660392 2016-06-27\n","4      79  2.174107  1.049557e+10  ...          0.170747  240.614773 2016-07-04\n","..    ...       ...           ...  ...               ...         ...        ...\n","207   283  3.405134  1.766778e+11  ...          1.788340  256.389586 2020-06-08\n","208   284  3.479638  1.775381e+11  ...          0.964811  256.375644 2020-06-15\n","209   285  3.470908  1.728119e+11  ...          1.152122  256.382639 2020-06-22\n","210   286  3.470784  1.726835e+11  ...          1.025005  256.398760 2020-06-29\n","211   287  3.708160  1.680863e+11  ...          1.311118  256.405000 2020-07-06\n","\n","[212 rows x 9 columns]"]},"metadata":{"tags":[]},"execution_count":63}]},{"cell_type":"code","metadata":{"id":"lIfBpZciY7t6","colab_type":"code","colab":{}},"source":["df.to_csv('bitcoin.csv')"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"CQJFNVZ41aq3","colab_type":"text"},"source":["#Procedure Example"]},{"cell_type":"code","metadata":{"id":"XYIEdbjQGb_i","colab_type":"code","colab":{}},"source":["import json\n","import urllib.request\n","\n","#https://api.blockchain.info/charts/transactions-per-second?timespan=2years&rollingAverage=8hours&format=json\n","with urllib.request.urlopen(\"https://api.blockchain.info/charts/market-cap?timespan=2years&rollingAverage=24hours&format=json\") as url:\n","  response = url.read()\n","  data = json.loads(response)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"XNM4ZkE_GiEi","colab_type":"code","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1593581055940,"user_tz":-120,"elapsed":601,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"2b29e347-1160-4b45-b12b-8d3baaf1403a"},"source":["import pandas as pd\n","data = pd.DataFrame(data)\n","data"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>status</th>\n","      <th>name</th>\n","      <th>unit</th>\n","      <th>period</th>\n","      <th>description</th>\n","      <th>values</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1532033668, 'y': 113149405085.26335}</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1532071949, 'y': 113626741608.18709}</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1532105067, 'y': 114123325317.08546}</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1532146275, 'y': 114565727637.77759}</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1532189461, 'y': 114921195536.95058}</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>1500</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1593376920, 'y': 172588083178.22244}</td>\n","    </tr>\n","    <tr>\n","      <th>1501</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1593419815, 'y': 172238575184.7884}</td>\n","    </tr>\n","    <tr>\n","      <th>1502</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1593458191, 'y': 171892578412.20844}</td>\n","    </tr>\n","    <tr>\n","      <th>1503</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1593493811, 'y': 171790844584.68216}</td>\n","    </tr>\n","    <tr>\n","      <th>1504</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>{'x': 1593534308, 'y': 171705869089.51547}</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>1505 rows × 6 columns</p>\n","</div>"],"text/plain":["     status  ...                                      values\n","0        ok  ...  {'x': 1532033668, 'y': 113149405085.26335}\n","1        ok  ...  {'x': 1532071949, 'y': 113626741608.18709}\n","2        ok  ...  {'x': 1532105067, 'y': 114123325317.08546}\n","3        ok  ...  {'x': 1532146275, 'y': 114565727637.77759}\n","4        ok  ...  {'x': 1532189461, 'y': 114921195536.95058}\n","...     ...  ...                                         ...\n","1500     ok  ...  {'x': 1593376920, 'y': 172588083178.22244}\n","1501     ok  ...   {'x': 1593419815, 'y': 172238575184.7884}\n","1502     ok  ...  {'x': 1593458191, 'y': 171892578412.20844}\n","1503     ok  ...  {'x': 1593493811, 'y': 171790844584.68216}\n","1504     ok  ...  {'x': 1593534308, 'y': 171705869089.51547}\n","\n","[1505 rows x 6 columns]"]},"metadata":{"tags":[]},"execution_count":71}]},{"cell_type":"code","metadata":{"colab_type":"code","id":"HS4-GvycGnGm","colab":{}},"source":["#make backup\n","data_copy = data.copy()"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"colab_type":"code","id":"pKhwa1CEGnGq","colab":{}},"source":["def extract_time(dict1):\n","  dict2 = list(dict1.values())\n","  #dict2 = list(data['values'][0].values())[0],\n","  return dict2\n","\n","def time_converter(time1):\n","  from datetime import datetime\n","  #return datetime.fromtimestamp(time1).strftime('%Y-%m-%d %H:%M:%S')\n","  return datetime.fromtimestamp(time1).strftime('%Y-%m-%d')"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"colab_type":"code","id":"gCn6INn5GnGs","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1593581064473,"user_tz":-120,"elapsed":708,"user":{"displayName":"Michelangiolo Mazzeschi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ghg8l8QeAQA6fd1iPVc2f_F0XQpum7zRjrF4R7HRw=s64","userId":"15738155355555420240"}},"outputId":"59d1946e-955c-4857-a275-016098d8cbfd"},"source":["data = data_copy.copy()\n","data['timestamp'] = data['values'].apply(lambda x : extract_time(x)[0])\n","data['timestamp'] = data['timestamp'].apply(lambda x : time_converter(x))\n","data['market cap'] = data['values'].apply(lambda x : extract_time(x)[1])\n","data.pop('values')\n","data"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>status</th>\n","      <th>name</th>\n","      <th>unit</th>\n","      <th>period</th>\n","      <th>description</th>\n","      <th>timestamp</th>\n","      <th>market cap</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>2018-07-19</td>\n","      <td>1.131494e+11</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>2018-07-20</td>\n","      <td>1.136267e+11</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>2018-07-20</td>\n","      <td>1.141233e+11</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>2018-07-21</td>\n","      <td>1.145657e+11</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>2018-07-21</td>\n","      <td>1.149212e+11</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>1500</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>2020-06-28</td>\n","      <td>1.725881e+11</td>\n","    </tr>\n","    <tr>\n","      <th>1501</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>2020-06-29</td>\n","      <td>1.722386e+11</td>\n","    </tr>\n","    <tr>\n","      <th>1502</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>2020-06-29</td>\n","      <td>1.718926e+11</td>\n","    </tr>\n","    <tr>\n","      <th>1503</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>2020-06-30</td>\n","      <td>1.717908e+11</td>\n","    </tr>\n","    <tr>\n","      <th>1504</th>\n","      <td>ok</td>\n","      <td>Market Capitalization</td>\n","      <td>USD</td>\n","      <td>day</td>\n","      <td>The total USD value of bitcoin supply in circu...</td>\n","      <td>2020-06-30</td>\n","      <td>1.717059e+11</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>1505 rows × 7 columns</p>\n","</div>"],"text/plain":["     status                   name  ...   timestamp    market cap\n","0        ok  Market Capitalization  ...  2018-07-19  1.131494e+11\n","1        ok  Market Capitalization  ...  2018-07-20  1.136267e+11\n","2        ok  Market Capitalization  ...  2018-07-20  1.141233e+11\n","3        ok  Market Capitalization  ...  2018-07-21  1.145657e+11\n","4        ok  Market Capitalization  ...  2018-07-21  1.149212e+11\n","...     ...                    ...  ...         ...           ...\n","1500     ok  Market Capitalization  ...  2020-06-28  1.725881e+11\n","1501     ok  Market Capitalization  ...  2020-06-29  1.722386e+11\n","1502     ok  Market Capitalization  ...  2020-06-29  1.718926e+11\n","1503     ok  Market Capitalization  ...  2020-06-30  1.717908e+11\n","1504     ok  Market Capitalization  ...  2020-06-30  1.717059e+11\n","\n","[1505 rows x 7 columns]"]},"metadata":{"tags":[]},"execution_count":74}]}]}